﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Configuration;
using Model.SystemManage;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
using System.Security.Cryptography;

namespace DAL.SystemDAL
{
    /// <summary>
    /// 员工信息访问类
    /// </summary>
    public class EmployeeInfo_Dal
    {
        private static string strCon = ConfigurationManager.ConnectionStrings["Constr"].ToString();

        #region 用户登录及登录后的操作

        //用户登录
        /// <summary>
        /// 员工登录
        /// </summary>
        /// <returns></returns>
        public EmployeeLoginInfo_Model IsLogin(string Account, string Password)
        {
            EmployeeLoginInfo_Model login = null;
            //数据库连接
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开连接
                conn.Open();
                Password = Md5Hash(Password);

                //sql操作语句
                string sql = "select * from EmployeeLoginInfo_Tab where Account ='" + Account + "' and Password='" + Password + "' and PersonState !=2";

                //数据库执行命令
                SqlCommand command = new SqlCommand(sql, conn);

                //数据库适配器
                SqlDataAdapter adr = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                adr.Fill(dt);

                if (dt.Rows.Count > 0)//判断Datatable是否有数据
                {
                    login = new EmployeeLoginInfo_Model();
                    login.Account = dt.Rows[0]["Account"].ToString();
                    login.EmployID = (int)dt.Rows[0]["EmployID"];
                    login.EmployName = dt.Rows[0]["EmployName"].ToString();
                    login.DeptID = (int)dt.Rows[0]["DeptID"];
                    login.PersonState = (int)dt.Rows[0]["PersonState"];
                    login.PositionID = (int)dt.Rows[0]["PositionID"];
                }
            }
            return login;
        }

        //判断登录后的用户是否有完整的基础信息
        /// <summary>
        /// 判断登录后的用户是否有完整的基础信息
        /// </summary>
        /// <param name="EmployID">员工ID</param>
        /// <returns></returns>
        public EmployeeBasicInfo_Model GetIsBasicInfo(int EmployID)
        {
            EmployeeBasicInfo_Model basic = null;
            //数据库连接
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开数据库
                conn.Open();

                //sql操作语句
                string sql = "select * from EmployeeBasicInfo_Tab where EmployID=" + EmployID;

                //数据库执行命令
                SqlCommand command = new SqlCommand(sql, conn);

                //数据库适配器
                SqlDataAdapter adr = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                adr.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    basic = new EmployeeBasicInfo_Model();
                    basic.Age = (int)dt.Rows[0]["Age"];
                    basic.Degree = dt.Rows[0]["Degree"].ToString();
                    basic.EducationID = (int)dt.Rows[0]["EducationID"];
                    basic.EmployeePhoto = dt.Rows[0]["EmployeePhoto"].ToString();
                    basic.EmployID = (int)dt.Rows[0]["EmployID"];
                    basic.IndustryBack = dt.Rows[0]["IndustryBack"].ToString();
                    basic.PoliticalID = (int)dt.Rows[0]["PoliticalID"];
                    basic.Sex = dt.Rows[0]["Sex"].ToString();
                    basic.Specialty = dt.Rows[0]["Specialty"].ToString();
                    basic.InfoState = (int)dt.Rows[0]["InfoState"];
                }
            }
            return basic;
        }


        #endregion

        #region 系统管理模块的显示操作


        #region 系统管理模块下拉框信息

        //获取政治面貌列表信息
        /// <summary>
        /// 获取政治面貌列表信息
        /// </summary>
        /// <returns></returns>
        public string GetPoliticalList()
        {
            DataTable dt = null;
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开数据库
                conn.Open();

                //sql操作语句
                string sql = "select * from Political_Tab";

                //数据库适配器
                SqlDataAdapter adr = new SqlDataAdapter(sql, conn);
                dt = new DataTable();
                adr.Fill(dt);

            }
            return JsonConvert.SerializeObject(dt);
        }

        //获取学历信息
        /// <summary>
        /// 获取学历列表信息
        /// </summary>
        /// <returns></returns>
        public string GetEducationList()
        {
            DataTable dt = null;
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开数据库
                conn.Open();

                //sql操作语句
                string sql = "select * from Education_Tab";

                //数据库适配器
                SqlDataAdapter adr = new SqlDataAdapter(sql, conn);
                dt = new DataTable();
                adr.Fill(dt);

            }
            return JsonConvert.SerializeObject(dt);
        }

        //获取职位信息
        /// <summary>
        /// 获取职位信息
        /// </summary>
        /// <returns></returns>
        public string GetPositionList()
        {
            DataTable dt = null;
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开数据库
                conn.Open();

                //sql操作语句
                string sql = "select * from Position_Tab";

                //数据库执行命令
                SqlCommand command = new SqlCommand(sql, conn);

                //读取器
                SqlDataAdapter adr = new SqlDataAdapter(command);
                dt = new DataTable();
                adr.Fill(dt);
            }
            return JsonConvert.SerializeObject(dt);
        }

        #endregion

        #region 有参数的操作

        //显示部门/角色信息，支持模糊查询
        /// <summary>
        /// 显示部门/角色信息，支持模糊查询
        /// </summary>
        /// <param name="DeptName">角色名称</param>
        /// <returns></returns>
        public List<Dept_Model> GetRoleList(string DeptName)
        {
            List<Dept_Model> list = null;
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                conn.Open();
                //sql执行语句
                string sql = "select * from Dept_Tab where DeptName like '%" + DeptName + "%'";

                //数据库执行命令
                SqlCommand command = new SqlCommand(sql, conn);

                //数据读取器
                SqlDataReader reader = command.ExecuteReader();

                //判断读取器是否有行数据
                if (reader.HasRows)
                {
                    list = new List<Dept_Model>();
                    //循环读取
                    while (reader.Read())
                    {
                        Dept_Model dept = new Dept_Model();
                        dept.DeptID = (int)reader["DeptID"];
                        dept.DeptName = reader["DeptName"].ToString();
                        list.Add(dept);
                    }
                }
                reader.Close();
            }
            return list;
        }

        //通过员工ID获取员工基础信息
        /// <summary>
        /// 通过员工ID获取员工基础信息
        /// </summary>
        /// <param name="EmployeeID"></param>
        /// <returns></returns>
        public EmployeeBasicInfo_Model GetBasicEmployeeInfoById(int EmployeeID)
        {
            EmployeeBasicInfo_Model basic = null;
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开数据库
                conn.Open();

                //sql操作语句
                string sql = "select * from EmployeeBasicInfo_Tab where EmployID =" + EmployeeID;

                //读取器
                SqlCommand command = new SqlCommand(sql, conn);
                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        basic = new EmployeeBasicInfo_Model();
                        basic.Age = (int)reader["Age"];
                        basic.Degree = reader["Degree"].ToString();
                        basic.EducationID = (int)reader["EducationID"];
                        basic.EmployeePhoto = reader["EmployeePhoto"].ToString();
                        basic.EmployID = (int)reader["EmployID"];
                        basic.IndustryBack = reader["IndustryBack"].ToString();
                        basic.PoliticalID = (int)reader["PoliticalID"];
                        basic.Sex = reader["Sex"].ToString();
                        basic.Specialty = reader["Specialty"].ToString();
                        basic.InfoState = (int)reader["InfoState"];
                    }
                }
                reader.Close();
            }
            return basic;
        }


        #endregion

        #region 获取对应的权限

        //定义一个静态集合
        public static List<LimitInfo_Model> LimitList = new List<LimitInfo_Model>();
        //权限信息符合查询条件总条数
        public int GetLimitCount(int DeptID, int PositionID)
        {
            int count;
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开数据库
                conn.Open();

                //sql操作语句
                string sql = "select COUNT(*) from LimitInfo2_Tab where menuid in(select LimitID from DeptConnLimit_Tab where PositionID =" + PositionID + " and DeptID = " + DeptID + ")";

                //数据库执行命令
                SqlCommand command = new SqlCommand(sql, conn);

                count = (int)command.ExecuteScalar();
            }
            return count;
        }
        //获取权限信息
        /// <summary>
        /// 获取权限信息
        /// </summary>
        /// <returns></returns>
        public List<LimitInfo_Model> GetLimitInfo(int DeptID, int PositionID)
        {
            if (LimitList == null || LimitList.Count != GetLimitCount(DeptID, PositionID))
            {
                LimitList.Clear();
                using (SqlConnection conn = new SqlConnection(strCon))
                {
                    //打开数据库
                    conn.Open();

                    //sql操作语句
                    string sql = "select * from LimitInfo2_Tab where menuid in(select LimitID from DeptConnLimit_Tab where PositionID =" + PositionID + " and DeptID = " + DeptID + ")";

                    //数据库执行命令
                    SqlCommand command = new SqlCommand(sql, conn);

                    //读取器
                    SqlDataReader reader = command.ExecuteReader();

                    //读取
                    if (reader.HasRows)
                    {

                        //循环赋值
                        while (reader.Read())
                        {
                            LimitInfo_Model limit = new LimitInfo_Model();
                            limit.menuid = (int)reader["menuid"];
                            limit.menuname = reader["menuname"].ToString();
                            limit.url = reader["url"].ToString();
                            limit.Pid = (int)reader["Pid"];
                            limit.icon = reader["icon"].ToString();
                            LimitList.Add(limit);
                        }
                    }
                }
            }
            return LimitList;
        }

        #endregion 

        #region 获取员工信息

        //员工列表静态列表
        public static List<EmployeeBasicInfo_Model> BasicList = new List<EmployeeBasicInfo_Model>();
        //员工信息总条数
        /// <summary>
        /// 员工信息总数
        /// </summary>
        /// <returns></returns>
        public int EmployeeInfoCount()
        {
            int count;
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                conn.Open();
                string sql = "select COUNT(*) from EmployeeLoginInfo_Tab A,EmployeeBasicInfo_Tab B where A.EmployID=B.EmployID";
                SqlCommand command = new SqlCommand(sql, conn);
                count = (int)command.ExecuteScalar();
            }
            return count;
        }
        /// <summary>
        /// 员工信息显示
        /// </summary>
        /// <returns></returns>
        public List<EmployeeBasicInfo_Model> GetEmployeeBasicInfo()
        {
            if (BasicList == null || BasicList.Count != EmployeeInfoCount())
            {
                using (SqlConnection conn = new SqlConnection(strCon))
                {
                    //打开数据库
                    conn.Open();
                    string sql = "select * from EmployeeLoginInfo_Tab A,EmployeeBasicInfo_Tab B where A.EmployID=B.EmployID";
                    SqlCommand command = new SqlCommand(sql, conn);
                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            EmployeeBasicInfo_Model EmpInfo = new EmployeeBasicInfo_Model();
                            EmpInfo.Account = reader["Account"].ToString();
                            EmpInfo.Age = (int)reader["Age"];
                            EmpInfo.Degree = reader["Degree"].ToString();
                            EmpInfo.DeptID = (int)reader["DeptID"];
                            EmpInfo.EducationID = (int)reader["EducationID"];
                            EmpInfo.EmployeePhoto = reader["EmployeePhoto"].ToString();
                            EmpInfo.EmployID = (int)reader["EmployID"];
                            EmpInfo.EmployName = reader["EmployName"].ToString();
                            EmpInfo.IndustryBack = reader["IndustryBack"].ToString();
                            EmpInfo.InfoState = (int)reader["InfoState"];
                            EmpInfo.PersonState = (int)reader["PersonState"];
                            EmpInfo.PoliticalID = (int)reader["PoliticalID"];
                            EmpInfo.PositionID = (int)reader["PositionID"];
                            EmpInfo.Sex = reader["Sex"].ToString();
                            EmpInfo.Specialty = reader["Specialty"].ToString();
                            BasicList.Add(EmpInfo);
                        }

                    }
                    reader.Close();
                }
            }
            return BasicList;
        }
        /// <summary>
        /// 分页/显示员工信息表
        /// </summary>
        /// <param name="PageIndex">当前索引</param>
        /// <param name="PageSize">每页多少条</param>
        /// <param name="count">记录总数量</param>
        /// <param name="EmployeeID">员工ID</param>
        /// <returns></returns>
        public List<EmployeeBasicInfo_Model> GetEmployeeBasicInfo(int PageIndex, int PageSize, out int count, int EmployeeID = 0)
        {
            List<EmployeeBasicInfo_Model> list = new List<EmployeeBasicInfo_Model>();
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开数据库
                conn.Open();

                //数据库执行命令
                SqlCommand command = new SqlCommand("Pro_Pager1", conn);
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter[] pars = 
                  {
                      new SqlParameter("@RecordCount",SqlDbType.Int),
                      new SqlParameter("@PageIndex",SqlDbType.Int),
                      new SqlParameter("@PageSize",SqlDbType.Int),
                      new SqlParameter("@orderString",SqlDbType.VarChar,50),
                      new SqlParameter("@whereString",SqlDbType.VarChar,1000),
                      new SqlParameter("@tableName",SqlDbType.VarChar,500),
                      new SqlParameter("@fieldName",SqlDbType.VarChar,500)
                  };

                pars[0].Direction = ParameterDirection.Output;
                pars[1].Value = PageIndex;
                pars[2].Value = PageSize;
                pars[3].Value = "A.EmployID";
                string whereString = " A.EmployID=B.EmployID and A.PositionID=C.PositionID and A.DeptID=D.DeptID";
                if (EmployeeID != 0)
                {
                    whereString += " and A.EmployID=" + EmployeeID;
                }

                pars[4].Value = whereString;
                pars[5].Value = "EmployeeLoginInfo_Tab A,EmployeeBasicInfo_Tab B,Position_Tab C,Dept_Tab D";
                pars[6].Value = "B.*,A.Account,A.EmployName,A.DeptID,A.PositionID,A.PersonState,C.PositionName,D.DeptName";

                command.Parameters.AddRange(pars);

                //sql读取器
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        EmployeeBasicInfo_Model EmpInfo = new EmployeeBasicInfo_Model();
                        EmpInfo.Account = reader["Account"].ToString();
                        EmpInfo.Age = (int)reader["Age"];
                        EmpInfo.Degree = reader["Degree"].ToString();
                        EmpInfo.DeptID = (int)reader["DeptID"];
                        EmpInfo.EducationID = (int)reader["EducationID"];
                        EmpInfo.EmployeePhoto = reader["EmployeePhoto"].ToString();
                        EmpInfo.EmployID = (int)reader["EmployID"];
                        EmpInfo.EmployName = reader["EmployName"].ToString();
                        EmpInfo.IndustryBack = reader["IndustryBack"].ToString();
                        EmpInfo.InfoState = (int)reader["InfoState"];
                        EmpInfo.PersonState = (int)reader["PersonState"];
                        EmpInfo.PoliticalID = (int)reader["PoliticalID"];
                        EmpInfo.PositionID = (int)reader["PositionID"];
                        EmpInfo.Sex = reader["Sex"].ToString();
                        EmpInfo.Specialty = reader["Specialty"].ToString();
                        EmpInfo.PositionName = reader["PositionName"].ToString();
                        EmpInfo.DeptName = reader["DeptName"].ToString();

                        list.Add(EmpInfo);
                    }

                }

                reader.Close();
                count = (int)pars[0].Value;
            }
            return list;
        }

        /// <summary>
        /// 分页/显示员工信息表
        /// </summary>
        /// <param name="PageIndex">当前索引</param>
        /// <param name="PageSize">每页多少条</param>
        /// <param name="count">记录总数量</param>
        /// <param name="EmployeeID">员工姓名</param>
        /// <returns></returns>
        public List<EmployeeBasicInfo_Model> GetEmployeeBasicInfo(int PageIndex, int PageSize, out int count, string EmployName)
        {
            List<EmployeeBasicInfo_Model> list = new List<EmployeeBasicInfo_Model>();
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开数据库
                conn.Open();

                //数据库执行命令
                SqlCommand command = new SqlCommand("Pro_Pager1", conn);
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter[] pars = 
                  {
                      new SqlParameter("@RecordCount",SqlDbType.Int),
                      new SqlParameter("@PageIndex",SqlDbType.Int),
                      new SqlParameter("@PageSize",SqlDbType.Int),
                      new SqlParameter("@orderString",SqlDbType.VarChar,50),
                      new SqlParameter("@whereString",SqlDbType.VarChar,1000),
                      new SqlParameter("@tableName",SqlDbType.VarChar,500),
                      new SqlParameter("@fieldName",SqlDbType.VarChar,500)
                  };

                pars[0].Direction = ParameterDirection.Output;
                pars[1].Value = PageIndex;
                pars[2].Value = PageSize;
                pars[3].Value = "A.EmployID";
                string whereString = " A.EmployID=B.EmployID and A.PositionID=C.PositionID and A.DeptID=D.DeptID";
                if (EmployName != "")
                {
                    whereString += " and A.EmployName like '%" + EmployName + "%'";
                }

                pars[4].Value = whereString;
                pars[5].Value = "EmployeeLoginInfo_Tab A,EmployeeBasicInfo_Tab B,Position_Tab C,Dept_Tab D";
                pars[6].Value = "B.*,A.Account,A.EmployName,A.DeptID,A.PositionID,A.PersonState,C.PositionName,D.DeptName";

                command.Parameters.AddRange(pars);

                //sql读取器
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        EmployeeBasicInfo_Model EmpInfo = new EmployeeBasicInfo_Model();
                        EmpInfo.Account = reader["Account"].ToString();
                        EmpInfo.Age = (int)reader["Age"];
                        EmpInfo.Degree = reader["Degree"].ToString();
                        EmpInfo.DeptID = (int)reader["DeptID"];
                        EmpInfo.EducationID = (int)reader["EducationID"];
                        EmpInfo.EmployeePhoto = reader["EmployeePhoto"].ToString();
                        EmpInfo.EmployID = (int)reader["EmployID"];
                        EmpInfo.EmployName = reader["EmployName"].ToString();
                        EmpInfo.IndustryBack = reader["IndustryBack"].ToString();
                        EmpInfo.InfoState = (int)reader["InfoState"];
                        EmpInfo.PersonState = (int)reader["PersonState"];
                        EmpInfo.PoliticalID = (int)reader["PoliticalID"];
                        EmpInfo.PositionID = (int)reader["PositionID"];
                        EmpInfo.Sex = reader["Sex"].ToString();
                        EmpInfo.Specialty = reader["Specialty"].ToString();
                        EmpInfo.PositionName = reader["PositionName"].ToString();
                        EmpInfo.DeptName = reader["DeptName"].ToString();

                        list.Add(EmpInfo);
                    }

                }

                reader.Close();
                count = (int)pars[0].Value;
            }
            return list;
        }

        #endregion



        #endregion

        #region 系统管理模块的添加操作

        //完善员工基础信息
        /// <summary>
        /// 添加员工基本信息
        /// </summary>
        /// <param name="basic"></param>
        /// <returns></returns>
        public int InsertBasicEmployeeInfo(EmployeeBasicInfo_Model basic)
        {
            int count;
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开数据库
                conn.Open();

                //sql操作语句
                string sql = string.Format("insert into EmployeeBasicInfo_Tab values({0},'{1}',{2},'{3}',{4},'{5}',{6},'{7}','{8}',{9})", basic.EmployID, basic.Sex, basic.Age, basic.EmployeePhoto, basic.EducationID, basic.Degree, basic.PoliticalID, basic.IndustryBack, basic.Specialty, 1);

                //数据库执行命令
                SqlCommand cmd = new SqlCommand(sql, conn);

                //返回受影响行数
                count = cmd.ExecuteNonQuery();
            }
            return count;
        }

        //添加部门/角色信息
        /// <summary>
        /// 添加部门/角色信息
        /// </summary>
        /// <param name="dept">部门实体类传输</param>
        /// <returns></returns>
        public int InsertDeptInfo(Dept_Model dept)
        {
            int count;
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开数据库
                conn.Open();

                //sql操作语句
                string sql = string.Format("insert into Dept_Tab values ('{0}')", dept.DeptName);

                //数据库执行命令
                SqlCommand command = new SqlCommand(sql, conn);

                //返回受影响的行数
                count = command.ExecuteNonQuery();
            }
            return count;
        }

        //为部门/职位分配权限
        /// <summary>
        /// 为部门/职位分配权限
        /// </summary>
        /// <param name="sql">sql操作语句</param>
        /// <returns></returns>
        public int GiveDeptAllotLimit(string sql)
        {
            int count;
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开数据库
                conn.Open();

                //数据库执行命令
                SqlCommand command = new SqlCommand(sql, conn);

                //返回受影响的行数
                count = command.ExecuteNonQuery();
            }
            return count;
        }

        //系统管理员添加用户登录信息
        /// <summary>
        /// 系统管理员注册员工
        /// </summary>
        /// <param name="register"></param>
        /// <returns></returns>
        public int RegisterEmployee(EmployeeLoginInfo_Model register)
        {
            int count;
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开数据库
                conn.Open();
                register.Password = Md5Hash(register.Password);
                //sql操作语句
                string sql = string.Format("insert into EmployeeLoginInfo_Tab values('{0}','{1}','{2}',{3},{4},{5})", register.Account, register.Password, register.EmployName, register.DeptID, register.PositionID, register.PersonState);

                //数据库执行命令
                SqlCommand command = new SqlCommand(sql, conn);

                //返回受影响的行数
                count = command.ExecuteNonQuery();
            }
            return count;
        }


        #endregion

        #region 系统管理模块的删除操作

        //删除个人基础信息
        /// <summary>
        /// 删除个人基础信息
        /// </summary>
        /// <param name="EmployeeId">员工ID</param>
        /// <returns></returns>
        public int DeleteBasicEmployeeInfoById(int EmployeeId)
        {
            int count;
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开数据库
                conn.Open();

                //sql操作语句
                string sql = "delete from EmployeeBasicInfo_Tab where EmployID =" + EmployeeId;

                //数据库执行命令
                SqlCommand cmd = new SqlCommand(sql, conn);

                //返回受影响的行数
                count = cmd.ExecuteNonQuery();
            }
            return count;
        }

        //删除权限关联表的信息
        /// <summary>
        /// 删除权限关联表的信息
        /// </summary>
        /// <param name="DeptId">部门id</param>
        /// <param name="PositionId">职位id</param>
        /// <returns></returns>
        public int DelLimitInfoByDeptIdAndPositionId(int DeptId, int PositionId)
        {
            int count;
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开数据库
                conn.Open();

                //sql操作语句
                string sql = "delete from DeptConnLimit_Tab where DeptID=" + DeptId + " and PositionID=" + PositionId;

                //数据库执行命令
                SqlCommand command = new SqlCommand(sql, conn);

                //返回受影响的行数
                count = command.ExecuteNonQuery();
            }
            return count;
        }

        #endregion

        #region 系统管理模块的修改操作

        //部门调整
        /// <summary>
        /// 部门调动
        /// </summary>
        /// <param name="EmployeeID">员工id</param>
        /// <param name="DeptID">部门id</param>
        /// <returns></returns>
        public int UpdateDept(int EmployeeID, int DeptID)
        {
            int count;
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                conn.Open();

                //sql操作语句
                string sql = "update EmployeeLoginInfo_Tab set DeptID =" + DeptID + " where EmployID =" + EmployeeID;

                //数据库执行命令
                SqlCommand command = new SqlCommand(sql, conn);

                count = command.ExecuteNonQuery();
            }
            return count;
        }


        //职位变更
        /// <summary>
        /// 职位变更
        /// </summary>
        /// <param name="EmployeeID">员工id</param>
        /// <param name="DeptID">职位id</param>
        /// <returns></returns>
        public int UpdatePosition(int EmployeeID, int PositionID)
        {
            int count;
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                conn.Open();

                //sql操作语句
                string sql = "update EmployeeLoginInfo_Tab set PositionID =" + PositionID + " where EmployID =" + EmployeeID;

                //数据库执行命令
                SqlCommand command = new SqlCommand(sql, conn);

                count = command.ExecuteNonQuery();
            }
            return count;
        }


        //修改员工基础信息审核状态
        /// <summary>
        /// 修改员工基础信息审核状态
        /// </summary>
        /// <param name="InfoState">信息状态</param>
        /// <param name="EmpId">员工Id</param>
        /// <returns></returns>
        public int UpdateBasicInfoState(int InfoState, int EmpId)
        {
            int count;
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                conn.Open();

                //sql操作语句
                string sql = "update EmployeeBasicInfo_Tab set InfoState =" + InfoState + " where EmployID =" + EmpId;

                //数据库执行命令
                SqlCommand command = new SqlCommand(sql, conn);

                count = command.ExecuteNonQuery();
            }
            return count;
        }


        //修改员工当前账号状态
        /// <summary>
        /// 修改员工当前账号状态
        /// </summary>
        /// <param name="InfoState">员工状态</param>
        /// <param name="EmpId">员工Id</param>
        /// <returns></returns>
        public int UpdateLoginInfoState(int PersonState, int EmpId)
        {
            int count;
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                conn.Open();

                //sql操作语句
                string sql = "update EmployeeLoginInfo_Tab set PersonState =" + PersonState + " where EmployID =" + EmpId;

                //数据库执行命令
                SqlCommand command = new SqlCommand(sql, conn);

                count = command.ExecuteNonQuery();
            }
            return count;
        }

        /// <summary>
        /// 修改用户密码
        /// </summary>
        /// <param name="EmpId">员工Id</param>
        /// <param name="newpass">新密码</param>
        /// <returns></returns>
        public string EditPassword(int EmpId, string newpass)
        {
            //数据库连接串
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                //打开数据库
                conn.Open();

                newpass = Md5Hash(newpass);
                //sql操作语句
                string sql = "update EmployeeLoginInfo_Tab set Password='" + newpass + "' where EmployID =" + EmpId;

                //sql操作命令
                SqlCommand command = new SqlCommand(sql, conn);

                //返回受影响的行数
                command.ExecuteNonQuery();
            }
            return newpass;
        }

        #endregion


        #region MD5加密

        /// <summary>
        /// 输入原密码返回加密后的密文
        /// </summary>
        /// <param name="input">输入的密码</param>
        /// <returns></returns>
        private string Md5Hash(string input)
        {
            MD5CryptoServiceProvider md5Hasher = new MD5CryptoServiceProvider();
            byte[] data = md5Hasher.ComputeHash(Encoding.Default.GetBytes(input));
            StringBuilder sBuilder = new StringBuilder();
            for (int i = 0; i < data.Length; i++)
            {
                sBuilder.Append(data[i].ToString("x2"));
            }
            return sBuilder.ToString();
        }

        #endregion
    }
}
